﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runda.CMS.Common.Entity;
using System.Data.Common;
using System.Data;

namespace Runda.CMS.Dal
{
    public partial class CtrtContractItemDal
    {
       
        /// <summary>
        /// 获取合同明细
        /// </summary>
        /// <param name="WhereClause"></param>
        /// <param name="OrderBy"></param>
        /// <returns></returns>
        public DataTable GetContractItemTable(string WhereClause,string OrderBy)
        {
            string sql = @"
select * from (
select A.*,B.instrumentCode,B.InstrumentName,B.vendor,B.Type, B.PresellPrice,C.PutDiscount,(A.Price * C.PutDiscount) as PutDiscountPrice  from Cms_Ctrt_ContractItem A join Cms_Ctrt_Instrument B 
ON A.instrumentGUID = B.instrumentGUID JOIN Cms_Ctrt_Contract C ON A.ContractID = C.ContractID
) AS T where 1=1 {0} {1}";

            sql = string.Format(sql, WhereClause,OrderBy);
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);

            return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

        /// <summary>
        /// 获取合同销售总价
        /// </summary>
        /// <param name="ContractID"></param>
        /// <returns></returns>
        //public decimal SumContractItemPrice(Guid ContractID)
        //{
        //    //string sql = "select isnull(sum(TotalPrice),0)+isnull(sum(PromiseRevenue * PutPeriod),0) from dbo.Cms_Ctrt_ContractItem where ParentID is null and ContractID = @ContractID";
        //    string sql = "select isnull(sum(PromiseRevenue * PutPeriod),0) from dbo.Cms_Ctrt_ContractItem where ParentID is null and ContractID = @ContractID";
        //    DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
        //    CurrentDatabase.AddInParameter(dbcommand, "@ContractID", DbType.Guid, ContractID);

        //    return (decimal)CurrentDatabase.ExecuteScalar(dbcommand);
        //}
        //投放合同试剂承诺销售额
        public decimal SumContractPutItemPrice(Guid ContractID)
        {
            //string sql = "select isnull(sum(TotalPrice),0)+isnull(sum(PromiseRevenue * PutPeriod),0) from dbo.Cms_Ctrt_ContractItem where ParentID is null and ContractID = @ContractID";
            string sql = "select isnull(sum(PromiseRevenue * PutPeriod),0) from dbo.Cms_Ctrt_ContractItem where ParentID is null and ContractID = @ContractID";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ContractID", DbType.Guid, ContractID);

            return (decimal)CurrentDatabase.ExecuteScalar(dbcommand);
        }

        //销售买断合同试剂销售额
        public decimal SumContractSaleItemPrice(Guid ContractID)
        {
            string sql = "select isnull(sum(TotalPrice),0) from dbo.Cms_Ctrt_ContractItem where ParentID is null and ContractID = @ContractID";
            
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ContractID", DbType.Guid, ContractID);

            return (decimal)CurrentDatabase.ExecuteScalar(dbcommand);
        }

        public decimal SumContractItemTotalPrice(Guid ContractID)
        {
            string sql = "select isnull(sum(Price * Count),0) from Cms_Ctrt_ContractItem where ContractID = @ContractID";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ContractID", DbType.Guid, ContractID);

            return (decimal)CurrentDatabase.ExecuteScalar(dbcommand);
        }

        public decimal SumContractItemCost(Guid ContractID)
        {
            string sql = "select isnull(sum(B.Cost * A.Count),0) from Cms_Ctrt_ContractItem A join Cms_Ctrt_Instrument B on A.instrumentGUID = B.instrumentGUID where A.ContractID = @ContractID";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ContractID", DbType.Guid, ContractID); 

            return (decimal)CurrentDatabase.ExecuteScalar(dbcommand);
        }

        /// <summary>
        /// 获取合同某类型成本
        /// </summary>
        /// <param name="ContractID"></param>
        /// <returns></returns>
        public decimal SumContractItemCost(Guid ContractID,int Type)
        {
            string sql = "select isnull(sum(B.Cost),0) from Cms_Ctrt_ContractItem A join Cms_Ctrt_Instrument B on A.instrumentGUID = B.instrumentGUID where A.ContractID = @ContractID and [Type] = @Type";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ContractID", DbType.Guid, ContractID);
            CurrentDatabase.AddInParameter(dbcommand, "@Type", DbType.Int32, Type);

            return (decimal)CurrentDatabase.ExecuteScalar(dbcommand);
        }

        /// <summary>
        /// 获取试剂成本
        /// </summary>
        /// <param name="ContractID"></param>
        /// <returns></returns>
        public decimal SumContractItemCostFor01(Guid ContractID)
        {
            string sql = "select (1 - (isnull(sum(A.Price),0) - isnull(sum(B.Cost),0))/ isnull(sum(A.Price),1) / (case count(1) when 0 then 1 else count(1) end)) * isnull(Sum(A.PromiseRevenue),0)   from Cms_Ctrt_ContractItem A join Cms_Ctrt_Instrument B on A.instrumentGUID = B.instrumentGUID where A.ContractID = @ContractID ";
            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@ContractID", DbType.Guid, ContractID); 

            return (decimal)CurrentDatabase.ExecuteScalar(dbcommand);
        }

    }
}
